package com.chinaoryx.er;

import org.apache.commons.lang.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by IntelliJ IDEA.
 * User: hyman.zhang
 * Date: 12-1-28
 * Time:
 */
public class TableDefination {
    private String tableName;



    private String aliasName;
    private TableDefination parentTable;


    private String[] columnNames;
    private String insertSql = null;

    private ColumnMap columnMap;
    private List<TableDefination> subTables;


    public String getAliasName() {
        if (StringUtils.isEmpty(this.aliasName))  {
            return this.tableName;
        }
        return aliasName;
    }

    public void setAliasName(String aliasName) {
        this.aliasName = aliasName;
    }



    public String getInsertSql() {
        return insertSql;
    }

    public void setInsertSql(String insertSql) {
        this.insertSql = insertSql;
    }

    public String[] getColumnNames() {
        return columnNames;
    }

    public void setColumnNames(String[] columnNames) {
        this.columnNames = columnNames;
    }

    public TableDefination getParentTable() {
        return parentTable;
    }

    public void setParentTable(TableDefination parentTable) {
        this.parentTable = parentTable;
    }


    public ColumnMap getColumnMap() {
        return columnMap;
    }

    public void setColumnMap(ColumnMap columnMap) {
        this.columnMap = columnMap;
    }

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }


    public List<TableDefination> getSubTables() {
        return subTables;
    }

    public void setSubTables(List<TableDefination> subTables) {
        this.subTables = subTables;
    }


    public List<Map<String, Object>> getRecordListByFk(Connection conn, Object fkValue) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        StringBuilder sql = new StringBuilder();
        sql.append("select * from ");
        sql.append(this.tableName);
        sql.append(" where ");
        sql.append(this.columnMap.getColumnName());
        sql.append("=?");

        pstmt = conn.prepareStatement(sql.toString());
        pstmt.setObject(1, fkValue);
        rs = pstmt.executeQuery();

        ResultSetMetaData rsmd = rs.getMetaData();
        int count = rsmd.getColumnCount();

        while (rs.next()) {
            Map<String, Object> result = new HashMap<String, Object>();
            for (int i = 0; i < count; i++) {
                String columnLabel = rsmd.getColumnLabel(i + 1);
                Object val = rs.getObject(i + 1);
                result.put(columnLabel, val);

            }
            list.add(result);
        }
        rs.close();
        pstmt.close();

        return list;
    }


    public void moveTableRecord(Connection frmConn, Connection toConn, Map<String, Object> record, Map<String, PreparedStatement> insertStmtMap) throws SQLException {
        
        // save current table


        PreparedStatement pstmt = null;
        pstmt = insertStmtMap.get(this.tableName);
        if (pstmt == null) {
            String insertSql = this.getInsertSql();
            pstmt = toConn.prepareStatement(insertSql);
            insertStmtMap.put(this.tableName, pstmt);
        }



        String[] columnNames = this.getColumnNames();
        int i = 1;
        for (String columnName : columnNames) {
            pstmt.setObject(i, record.get(columnName));
            i++;

        }

        pstmt.addBatch();
//        pstmt.execute();
//        pstmt.close();

        // move sub tables
        List<TableDefination> subTables = this.getSubTables();
        if (subTables != null) {
            for (TableDefination subTable : subTables) {
                String parentColumnName = subTable.getColumnMap().getParentColumnName();

                Object value = record.get(parentColumnName.toUpperCase());
                List<Map<String, Object>> recordList = subTable.getRecordListByFk(frmConn, value);
                if (recordList != null) {
                    for (Map<String, Object> subRecord : recordList) {
                        subTable.moveTableRecord(frmConn, toConn, subRecord, insertStmtMap);
//                        this.saveTable(frmConn, toConn, subTable, subRecord);
                    }
                }

            }
        }
    }


    public void  genDeleteSql(List<String> sqlList){


        if (this.subTables != null) {
            for (TableDefination subTable: this.subTables) {
                  subTable.genDeleteSql( sqlList);
            }
        }
        //genearate delete sql
        StringBuilder sql = new StringBuilder();
        sql.append("delete ");
        sql.append(this.aliasName);
        sql.append("\r\n");
        sql.append(" from ");
        sql.append(this.tableName).append(" as ").append(this.aliasName);
        TableDefination curTable = this;
        TableDefination parentTable = this.getParentTable();
//        sb.append("\r\n");
        while (parentTable != null) {
            sql.append(" join ");
            sql.append(parentTable.getTableName()).append(" as ").append(parentTable.aliasName);
            sql.append(" on ");
            sql.append("(");
            TableDefination.ColumnMap cm = curTable.getColumnMap();
            sql.append(curTable.aliasName).append(".").append(cm.getColumnName());
            sql.append("=");
            sql.append(parentTable.aliasName).append(".").append(cm.getParentColumnName());
            sql.append(")");
            sql.append("\r\n");
            curTable = parentTable;
            parentTable = parentTable.getParentTable();
        }

        sqlList.add(sql.toString());

//        sb.append(" where xxxxxxxxxx");
        sql.append("\r\n");
//        sb.append("\r\n");





        // delete subTables

        // delete cur table

    }


    public void initInsertSql() {
        StringBuilder sb = new StringBuilder();
        sb.append("insert into ");
        sb.append(this.tableName);
        sb.append(" (");
        boolean isFirst = true;
        for (String columnName : columnNames) {
            if (!isFirst) {
                sb.append(",");
            } else {
                isFirst = false;
            }
            sb.append(columnName);
        }
        sb.append(" )");
        sb.append(" values (");
        isFirst = true;
        for (String columnName : columnNames) {
            if (!isFirst) {
                sb.append(",");
            } else {
                isFirst = false;
            }
            sb.append("?");
        }
        sb.append(")");

        this.insertSql = sb.toString();
        System.out.println(this.insertSql);
    }

    public static class ColumnMap {
        String columnName;
        String parentColumnName;

        public String getColumnName() {
            return columnName;
        }

        public void setColumnName(String columnName) {
            this.columnName = columnName;
        }

        public String getParentColumnName() {
            return parentColumnName;
        }

        public void setParentColumnName(String parentColumnName) {
            this.parentColumnName = parentColumnName;
        }
    }
}
